package com.sunyard.layoutit.common;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 数据库配置信息
 * 
 * @author gdl
 * 
 */
public class DatabaseConfig {

	private static final Logger logger = LoggerFactory.getLogger(DatabaseConfig.class);

	/**
	 * 数据库类型
	 */
	public static final String DATABASE_TYPE_H2 = "h2";
	public static final String DATABASE_TYPE_MYSQL = "mysql";
	public static final String DATABASE_TYPE_ORACLE = "oracle";
	public static final String DATABASE_TYPE_POSTGRES = "postgres";
	public static final String DATABASE_TYPE_MSSQL = "mssql";
	public static final String DATABASE_TYPE_DB2 = "db2";
	
	/**
	 * 数据库数据表前缀
	 */
	public static final String TABLE_PREFIX = "" ;
	
	public static final String BLOB_CHARSET = "gbk" ;

	protected static final Map<String, Map<String, String>> databaseSpecificStatements = new HashMap<String, Map<String, String>>();

	public static final Map<String, String> databaseSpecificLimitBeforeStatements = new HashMap<String, String>();
	public static final Map<String, String> databaseSpecificLimitAfterStatements = new HashMap<String, String>();
	public static final Map<String, String> databaseSpecificLimitBetweenStatements = new HashMap<String, String>();
	public static final Map<String, String> databaseSpecificOrderByStatements = new HashMap<String, String>();
	public static final Map<String, String> databaseOuterJoinLimitBetweenStatements = new HashMap<String, String>();
	public static final Map<String, String> databaseSpecificLimitBeforeNativeQueryStatements = new HashMap<String, String>();

	static {
	    String defaultOrderBy = " order by ${orderBy} ";
	    
	    // h2
	    databaseSpecificLimitBeforeStatements.put("h2", "");
	    databaseSpecificLimitAfterStatements.put("h2", "LIMIT #{maxResults} OFFSET #{firstResult}");
	    databaseSpecificLimitBetweenStatements.put("h2", "");
	    databaseOuterJoinLimitBetweenStatements.put("h2", "");
	    databaseSpecificOrderByStatements.put("h2", defaultOrderBy);
	    
		  //mysql specific
	    databaseSpecificLimitBeforeStatements.put("mysql", "");
	    databaseSpecificLimitAfterStatements.put("mysql", "LIMIT #{maxResults} OFFSET #{firstResult}");
	    databaseSpecificLimitBetweenStatements.put("mysql", "");
	    databaseOuterJoinLimitBetweenStatements.put("mysql", "");
	    databaseSpecificOrderByStatements.put("mysql", defaultOrderBy);
	    
	    //postgres specific
	    databaseSpecificLimitBeforeStatements.put("postgres", "");
	    databaseSpecificLimitAfterStatements.put("postgres", "LIMIT #{maxResults} OFFSET #{firstResult}");
	    databaseSpecificLimitBetweenStatements.put("postgres", "");
	    databaseOuterJoinLimitBetweenStatements.put("postgres", "");
	    databaseSpecificOrderByStatements.put("postgres", defaultOrderBy);
	        
	    // oracle
	    databaseSpecificLimitBeforeStatements.put("oracle", "select * from ( select a.*, ROWNUM rnum from (");
	    databaseSpecificLimitAfterStatements.put("oracle", "  ) a where ROWNUM < #{lastRow}) where rnum  >= #{firstRow}");
	    databaseSpecificLimitBetweenStatements.put("oracle", "");
	    databaseOuterJoinLimitBetweenStatements.put("oracle", "");
	    databaseSpecificOrderByStatements.put("oracle", defaultOrderBy);

	    // db2
	    databaseSpecificLimitBeforeStatements.put("db2", "SELECT SUB.* FROM (");
	    databaseSpecificLimitAfterStatements.put("db2", ")RES ) SUB WHERE SUB.rnk >= #{firstRow} AND SUB.rnk < #{lastRow}");
	    databaseSpecificLimitBetweenStatements.put("db2", ", row_number() over (ORDER BY ${orderBy}) rnk FROM ( select distinct RES.* ");
	    databaseOuterJoinLimitBetweenStatements.put("db2", ", row_number() over (ORDER BY ${mssqlOrDB2OrderBy}) rnk FROM ( select distinct ");
	    databaseSpecificOrderByStatements.put("db2", "");
	    databaseSpecificLimitBeforeNativeQueryStatements.put("db2", "SELECT SUB.* FROM ( select RES.* , row_number() over (ORDER BY ${orderBy}) rnk FROM (");

	    // mssql
	    databaseSpecificLimitBeforeStatements.put("mssql", "SELECT SUB.* FROM (");
	    databaseSpecificLimitAfterStatements.put("mssql", ")RES ) SUB WHERE SUB.rnk >= #{firstRow} AND SUB.rnk < #{lastRow}");
	    databaseSpecificLimitBetweenStatements.put("mssql", ", row_number() over (ORDER BY ${orderBy}) rnk FROM ( select distinct RES.* ");
	    databaseOuterJoinLimitBetweenStatements.put("mssql", ", row_number() over (ORDER BY ${mssqlOrDB2OrderBy}) rnk FROM ( select distinct ");
	    databaseSpecificOrderByStatements.put("mssql", "");
	    databaseSpecificLimitBeforeNativeQueryStatements.put("mssql", "SELECT SUB.* FROM ( select RES.* , row_number() over (ORDER BY ${orderBy}) rnk FROM (");
	}

	protected static String databaseType;
	protected static String databaseTablePrefix = "";
	private boolean tablePrefixIsSchema;

	protected String databaseCatalog;
	/**
	 * In some situations you want to set the schema to use for table checks /
	 * generation if the database metadata doesn't return that correctly, see
	 * https://jira.codehaus.org/browse/ACT-1220,
	 * https://jira.codehaus.org/browse/ACT-1062
	 */
//	protected boolean isDbIdentityUsed = true;
//	protected boolean isDbHistoryUsed = true;
//	protected boolean isOptimizeDeleteOperationsEnabled;

//	private String getStatement(Class<?> persistentObjectClass,
//			Map<Class<?>, String> cachedStatements, String prefix) {
//		String statement = cachedStatements.get(persistentObjectClass);
//		if (statement != null) {
//			return statement;
//		}
//		statement = prefix + persistentObjectClass.getSimpleName();
//		statement = statement.substring(0, statement.length() - 6); // removing
//																	// 'entity'
//		cachedStatements.put(persistentObjectClass, statement);
//		return statement;
//	}

	// db specific mappings
	// /////////////////////////////////////////////////////

//	protected static void addDatabaseSpecificStatement(String databaseType,
//			String activitiStatement, String ibatisStatement) {
//		Map<String, String> specificStatements = databaseSpecificStatements
//				.get(databaseType);
//		if (specificStatements == null) {
//			specificStatements = new HashMap<String, String>();
//			databaseSpecificStatements.put(databaseType, specificStatements);
//		}
//		specificStatements.put(activitiStatement, ibatisStatement);
//	}

//	public String mapStatement(String statement) {
//		if (statementMappings == null) {
//			return statement;
//		}
//		String mappedStatement = statementMappings.get(statement);
//		return (mappedStatement != null ? mappedStatement : statement);
//	}

	// customized getters and setters
	// ///////////////////////////////////////////

	public static void setDatabaseType(String _databaseType) {
		databaseType = _databaseType;
//		this.statementMappings = databaseSpecificStatements.get(databaseType);
	}

	public static String getDatabaseType() {
		return databaseType;
	}

	public static void setDatabaseTablePrefix(String _databaseTablePrefix) {
		databaseTablePrefix = _databaseTablePrefix;
	}

	public static String getDatabaseTablePrefix() {
		return databaseTablePrefix;
	}

	public String getDatabaseCatalog() {
		return databaseCatalog;
	}

	public void setDatabaseCatalog(String databaseCatalog) {
		this.databaseCatalog = databaseCatalog;
	}

	public void setTablePrefixIsSchema(boolean tablePrefixIsSchema) {
		this.tablePrefixIsSchema = tablePrefixIsSchema;
	}

	public boolean isTablePrefixIsSchema() {
		return tablePrefixIsSchema;
	}

	protected static Properties databaseTypeMappings = getDefaultDatabaseTypeMappings();

	protected static Properties getDefaultDatabaseTypeMappings() {
		Properties databaseTypeMappings = new Properties();
		databaseTypeMappings.setProperty("H2", DATABASE_TYPE_H2);
		databaseTypeMappings.setProperty("MySQL", DATABASE_TYPE_MYSQL);
		databaseTypeMappings.setProperty("Oracle", DATABASE_TYPE_ORACLE);
		databaseTypeMappings.setProperty("PostgreSQL", DATABASE_TYPE_POSTGRES);
		databaseTypeMappings.setProperty("Microsoft SQL Server",DATABASE_TYPE_MSSQL);
		databaseTypeMappings.setProperty(DATABASE_TYPE_DB2, DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty(DATABASE_TYPE_DB2, DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/NT", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/NT64", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2 UDP", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/LINUX", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/LINUX390", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/LINUXX8664", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/LINUXZ64", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/400 SQL", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/6000", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2 UDB iSeries", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/AIX64", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/HPUX", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/HP64", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/SUN", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/SUN64", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/PTX", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2/2", DATABASE_TYPE_DB2);
		databaseTypeMappings.setProperty("DB2 UDB AS400", DATABASE_TYPE_DB2);
		return databaseTypeMappings;
	}

	public static void initDatabaseType(Connection connection) {
		try {
			DatabaseMetaData databaseMetaData = connection.getMetaData();
			String databaseProductName = databaseMetaData.getDatabaseProductName();
			logger.debug("database product name: '{}'", databaseProductName);
			databaseType = databaseTypeMappings.getProperty(databaseProductName);
			if (databaseType == null) {
				throw new RuntimeException("couldn't deduct database type from database product name '" + databaseProductName + "'");
			}
			logger.debug("using database type: {}", databaseType);

		} catch (SQLException e) {
			logger.error("Exception while initializing Database connection", e);
		} finally {
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error("Exception while closing the Database connection",e);
			}
		}
	}
	
	/**
	 * 获取数据库配置参数
	 */
	private static Properties jdbcprop = null ;
	
	private static String JDBC_DRIVER ;
	private static String JDBC_URL ;
	private static String JDBC_USERNAME ;
	private static String JDBC_PASSWORD ;
	private static int	JDBC_MAXACTIVE ;
	private static String JDBC_DATASOURCE ;
	
	private static DataSource dataSource ;
	
	
	
	public static Properties getJdbcProperties(){
		if(null != jdbcprop){
			return jdbcprop ;
		}
		jdbcprop = new Properties();
		InputStream in = ApplicationConfig.class.getResourceAsStream("/jdbc.properties");
		try {
			jdbcprop.load(in);
			JDBC_DATASOURCE = jdbcprop.getProperty("jdbc.datasource");
			JDBC_DRIVER = jdbcprop.getProperty("jdbc.driver");
			JDBC_URL = jdbcprop.getProperty("jdbc.url");
			JDBC_USERNAME = jdbcprop.getProperty("jdbc.username");
			JDBC_PASSWORD = jdbcprop.getProperty("jdbc.password");
			JDBC_MAXACTIVE = Integer.valueOf(jdbcprop.getProperty("jdbc.maxActive"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		return jdbcprop ;
	}
	
	/**
	 * 初始化
	 */
	public static void init(DataSource _dataSource){
		Connection conn = null ;
		try {
			if(dataSource != null){
				dataSource = _dataSource ;
				conn = dataSource.getConnection() ;
			}else{
				conn = getConnection() ;
				initDatasource();
			}
			initDatabaseType(conn);//该方法中已经关闭数据库链接
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("数据库配置初始化异常",e);
		}
	}
	
	/**
	 * 获取数据库链接
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException{
		if(null == dataSource){
			if(null == jdbcprop){
				getJdbcProperties();
			}
			try {
				Class.forName(JDBC_DRIVER);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
				logger.error("为找到jdbc驱动类 {}",JDBC_DRIVER);
				throw new RuntimeException(e);
			}
			Connection connection = DriverManager.getConnection(JDBC_URL,JDBC_USERNAME,JDBC_PASSWORD);
			return connection ;
		}else{
			return dataSource.getConnection() ;
		}
	}
	
	public static DataSource getDataSource(){
		return dataSource ;
	}
	
	private static final String DATASOURCE_TYPE_DRUID = "druid";
	private static final String DATASOURCE_TYPE_C3P0 = "c3p0";
	private static final String DATASOURCE_TYPE_DBCP = "dbcp";
	
	/**
	 * 初始化数据源
	 * druid | c3p0 | dbcp
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws NoSuchMethodException 
	 * @throws SecurityException 
	 * @throws NoSuchFieldException 
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 */
	private static void initDatasource() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SecurityException, NoSuchMethodException, NoSuchFieldException, IllegalArgumentException, InvocationTargetException{
		if(DATASOURCE_TYPE_DRUID.equalsIgnoreCase(JDBC_DATASOURCE)){
			Class clazz = Class.forName("com.alibaba.druid.pool.DruidDataSource") ;
//			com.alibaba.druid.pool.DruidDataSource d ;
//			for(Field f : clazz.getDeclaredFields()){
//				System.out.println("Field \t-->\t" + f.getName());
//			}
//			for(Method m : clazz.getMethods()){
//				System.out.println("Field \t-->\t" + m.getName());
//			}
			Object obj = clazz.newInstance();
			clazz.getMethod("setDriverClassName", String.class).invoke(obj, JDBC_DRIVER);
			clazz.getMethod("setUrl", String.class).invoke(obj, JDBC_URL);
			clazz.getMethod("setUsername", String.class).invoke(obj, JDBC_USERNAME);
			clazz.getMethod("setPassword", String.class).invoke(obj, JDBC_PASSWORD);
			clazz.getMethod("setMaxActive", int.class).invoke(obj, JDBC_MAXACTIVE);
			
//			clazz.getDeclaredField("driverClassName").set(obj, JDBC_DRIVER);
//			clazz.getDeclaredField("url").set(obj, JDBC_URL);
//			clazz.getDeclaredField("username").set(obj, JDBC_USERNAME);
//			clazz.getDeclaredField("password").set(obj, JDBC_PASSWORD);
//			clazz.getDeclaredField("maxActive").set(obj, JDBC_MAXACTIVE);
			dataSource = (DataSource) obj ;
		}else if(DATASOURCE_TYPE_C3P0.equalsIgnoreCase(JDBC_DATASOURCE)){
			Class clazz = Class.forName("com.mchange.v2.c3p0.ComboPooledDataSource") ;
			Object obj = clazz.newInstance();  
			clazz.getDeclaredField("driverClass").set(obj, JDBC_DRIVER);
			clazz.getDeclaredField("jdbcUrl").set(obj, JDBC_URL);
			clazz.getDeclaredField("user").set(obj, JDBC_USERNAME);
			clazz.getDeclaredField("password").set(obj, JDBC_PASSWORD);
			dataSource = (DataSource) obj ;
		}else if(DATASOURCE_TYPE_DBCP.equalsIgnoreCase(JDBC_DATASOURCE)){
			Class clazz = Class.forName("org.apache.commons.dbcp.BasicDataSource") ;
			Object obj = clazz.newInstance();  
			clazz.getDeclaredField("driverClassName").set(obj, JDBC_DRIVER);
			clazz.getDeclaredField("url").set(obj, JDBC_URL);
			clazz.getDeclaredField("user").set(obj, JDBC_USERNAME);
			clazz.getDeclaredField("password").set(obj, JDBC_PASSWORD);
			dataSource = (DataSource) obj ;
		}else{
			throw new RuntimeException("未知的数据源类型");
		}
	}
	
	private static Map<String,Integer> maxIdMap = new HashMap<String,Integer>();
	
	/**
	 * 查询表最大ID值
	 * @param tableName
	 * @return
	 */
	public static int queryMaxId(String tableName){
		if(maxIdMap.containsKey(tableName.toLowerCase())){
			int res = maxIdMap.get(tableName.toLowerCase()) ;
			return res ;
		}
		String _tableName = TABLE_PREFIX + tableName ;
		String sql = null ;
		if(databaseType.equals(DATABASE_TYPE_H2)){
			sql = "select nvl(max(CONVERT(t.ID_,int)),0) cc from "+_tableName+" t";
		}else if(databaseType.equals(DATABASE_TYPE_MYSQL)){
			//TODO ...
			throw new RuntimeException("尚未完成,待补充");
		}else if(databaseType.equals(DATABASE_TYPE_ORACLE)){
			sql = "select nvl(max(to_number(t.ID_)),0) cc from "+_tableName+" t";
		}else if(databaseType.equals(DATABASE_TYPE_POSTGRES)){
			//TODO ...
			throw new RuntimeException("尚未完成,待补充");
		}else if(databaseType.equals(DATABASE_TYPE_MSSQL)){
			//TODO ...
			throw new RuntimeException("尚未完成,待补充");
		}else if(databaseType.equals(DATABASE_TYPE_DB2)){
			sql = "select nvl(max(cast(t.ID_ as integer)),0) cc from "+_tableName+" t";
		}else{
			throw new RuntimeException("未知的数据类型");
		}
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			BigDecimal b = (BigDecimal) runner.query(sql, new ScalarHandler());
			maxIdMap.put(tableName.toLowerCase(), b.intValue());
			return b.intValue() ;
//			return (Integer)(runner.query(sql, new ScalarHandler()));
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	
	public static int queryNextId(String tableName){
		int maxid = queryMaxId(tableName) ;
		int nextid = maxid + 1 ;
		maxIdMap.put(tableName.toLowerCase(), nextid);
		return nextid ;
	}

}
